package com.sj.purchase.modules.cost.repository.imei;

import com.sj.purchase.modules.cost.domain.imei.ImeiId;
import com.sj.purchase.modules.cost.domain.imei.Imei;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.Date;
import java.util.List;

/**
 * Created by yangrd on 2017/7/20.
 */
public interface ImeiRepository extends CrudRepository<Imei, ImeiId> {

    /**
     * 获取期初库存所有的imei
     *
     * @param sku
     * @param startTime
     * @return
     */
    @Query(value = "SELECT * from imei t1 " +
            "WHERE t1.sku =?1 " +
            "AND t1.imei_type = 'IN' AND t1.create_date<= ?2 " +
            "AND NOT EXISTS ( " +
            "SELECT t.imei FROM imei t " +
            "WHERE  t.imei = t1.imei " +
            "AND t.sku =?1 " +
            "AND t.imei_type = 'OUT' AND t.create_date<= ?2) ", nativeQuery = true)
    List<Imei> findStock(String sku, Date startTime);

    /**
     * 获取期初库的某天之前某个sku的所有的imei
     */
    @Query(
            value = "SELECT * from imei t1 " +
                    "WHERE t1.sku LIKE ?2 " +
                    "AND t1.imei_type = 'IN' AND t1.create_date<= ?1 " +
                    "AND NOT EXISTS ( " +
                    "SELECT t.imei FROM imei t " +
                    "WHERE  t.imei = t1.imei " +
                    "AND t.sku LIKE ?2 " +
                    "AND t.imei_type = 'OUT' AND t.create_date<= ?1) ORDER BY ?#{#pageable}",
            countQuery = "SELECT COUNT(*) from imei t1 " +
                    "WHERE t1.sku LIKE ?2 " +
                    "AND t1.imei_type = 'IN' AND t1.create_date<= ?1 " +
                    "AND NOT EXISTS ( " +
                    "SELECT t.imei FROM imei t " +
                    "WHERE  t.imei = t1.imei " +
                    "AND t.sku LIKE ?2 " +
                    "AND t.imei_type = 'OUT' AND t.create_date<= ?1) ", nativeQuery = true)
    Page<Imei> findByDayAndSku(Date day, String skuLike, Pageable pageable);


    //入库明细: 获取某一天（选择等于而不是between的原因 CreateDate数据格式yyyy-MM-dd:00:00:00）某一个入库单下的某个spu对应的imei
    @Query(value = "SELECT " +
            "imei.* " +
            "FROM " +
            "imei " +
            "INNER JOIN v_spu_sku ON v_spu_sku.sku = imei.sku " +
            "where imei.create_date = ?1 and imei.order_no = ?2 and v_spu_sku.sku = ?3 and imei.imei_type = 'IN' ORDER BY ?#{#pageable}", countQuery = "" +
            "SELECT " +
            "count(*) " +
            "FROM " +
            "imei " +
            "INNER JOIN v_spu_sku ON v_spu_sku.sku = imei.sku " +
            "where imei.create_date = ?1 and imei.order_no = ?2 and v_spu_sku.sku = ?3 and imei.imei_type = 'IN' ", nativeQuery = true)
    Page<Imei> findInImeiByCreateDateAndOrderNoAndSku(Date day, String orderNo, String sku, Pageable pageable);

    //按imei和imei类型查询imei
    Imei findByIdImeiAndAndImeiType(String imei, Imei.ImeiType imeiType);

    //出库明细: 获取某一天某个spu对应的imei
    @Query(value = "SELECT " +
            "Imei.* " +
            "FROM " +
            "Imei " +
            "INNER JOIN v_spu_sku ON v_spu_sku.sku = Imei.sku " +
            "where  Imei.create_date = ?1 and v_spu_sku.sku = ?2 and Imei.imei_type = 'OUT' ORDER BY ?#{#pageable} ", countQuery = "SELECT " +
            "count(*) " +
            "FROM " +
            "Imei " +
            "INNER JOIN v_spu_sku ON v_spu_sku.sku = Imei.sku " +
            "where  Imei.create_date = ?1 and v_spu_sku.sku = ?2 and Imei.imei_type = 'OUT'  ", nativeQuery = true)
    Page<Imei> findOutImeiByByCreateDateAndSku(Date day, String sku, Pageable pageable);

    /**
     * 获取某日期以前某库存的sku所有订单号及数量
     *
     * @param sku
     * @param startTime
     * @return
     */
    @Query(value = "SELECT a1.order_no,a1.create_date,count(imei) FROM (" +
            "SELECT * from imei t1 " +
            "WHERE t1.sku =?1 " +
            "AND t1.imei_type = 'IN' AND t1.create_date<= ?2 " +
            "AND NOT EXISTS ( " +
            "SELECT t.imei FROM imei t " +
            "WHERE  t.imei = t1.imei " +
            "AND t.sku =?1 " +
            "AND t.imei_type = 'OUT' AND t.create_date<= ?2)" +
            ") a1 GROUP BY a1.order_no,a1.create_date", nativeQuery = true)
    List<Object[]> findStockGroupOrder(String sku, Date startTime);

    @Query(value = "SELECT * from imei t where t.sku=?1 and DATE_FORMAT(t.create_date,'%Y-%m-%d')=?2 and t.imei_type=?3", nativeQuery = true)
    List<Imei> findBySkuDay(String sku, String day, String type);

    Imei findDistinctFirstByIdImeiAndSkuAndImeiType(String imei, String sku, Imei.ImeiType type);
}
